package month.statistic;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import constant.FileLocation;
import constant.TripFeature;
import entity.geometry.MultiPolygonRing;
import entity.geometry.MultiPolygonStreetArea;
import entity.geometry.POI;
import entity.geometry.Point;
import entity.month.PointCoordinate;
import entity.month.UserPortrait;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.database.PreprocessPOIData;
import utility.database.SqlServerAccess;
import utility.file.PropertiesLoader;
import utility.geometry.ProcessPoint;
import utility.geometry.ProcessGeoJson;
import utility.time.ProcessTime;

import java.sql.*;
import java.util.*;

/**
 * 处理用户画像数据表
 * @author lit
 * @date 2018/3/20.
 */
public class ProcessUserPortrait {

    //数据连接
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;

    //用于POI
    private ArrayList<Map<Long, HashSet<POI>>> poiIndexList = null;
    private PreprocessPOIData preprocessPOIData = null;

    //用于输出信息
    private Logger logger = null;

    //用于获取街道区域、环路ID
    private ArrayList<MultiPolygonStreetArea> streetAreaArray = null;
    private ArrayList<MultiPolygonRing> ringArray = null;

    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void processUserPortrait(String[] statTime){
        logger = LoggerFactory.getLogger(ProcessUserPortrait.class);
        logger.info("即将开始进行智驾盒子月统计计算");
        Map<String,UserPortrait> userPortraitMap = new HashMap<>(1024);
        logger.info("获取用户画像基础信息");
        getUserBaseField(statTime,userPortraitMap);

        //获取poi坐标的序列化列表，用来确定居住地和工作的POI
        logger.info("获取POI基础数据");
        preprocessPOIData = new PreprocessPOIData();
        ArrayList<POI> pois = preprocessPOIData.loadPOIFile();
        poiIndexList = preprocessPOIData.generateIndex(pois);
        //获取街道区域环路的坐标信息，用于确定点所在的街道区域和环路
        logger.info("获取街道区域和环路基础数据");
        String streetAreaFilePath = PropertiesLoader.getParameter(FileLocation.STREETAREA_GEOJSON_FILE_KEY,FileLocation.STREETAREA_GEOJSON_FILE_DEFAULT);
        streetAreaArray = ProcessGeoJson.importStreetAreaGeojsonFile(streetAreaFilePath);
        String ringFilePath = PropertiesLoader.getParameter(FileLocation.RING_GEOJSON_FILE_KEY,FileLocation.RING_GEOJSON_FILE_DEFAULT);
        ringArray = ProcessGeoJson.importRingGeojsonFile(ringFilePath);

        int year = ProcessTime.getYear(statTime[1]);
        int month = ProcessTime.getMonth(statTime[1]);
        logger.info("即将清除用户画像已有{}年{}月数据",year,month);
        clearUserPortrait(year,month);

        logger.info("获取居住地数据");
        getHomeCoordinate(statTime,userPortraitMap);

        logger.info("获取工作地数据");
        getWorkCoordinate(statTime,userPortraitMap);

        getMonthFieldInfo(statTime,userPortraitMap);

        logger.info("获取出行特征");
        getTripFeature(statTime,userPortraitMap);

        logger.info("获取车辆权重");
        getBoxWeight(userPortraitMap);

        logger.info("用户画像数据插入数据库");
        addUserPortraitData(userPortraitMap);

        logger.info("用户画像数据表操作结束");
        finishMSSQL();
    }

    private void clearUserPortrait(int year, int month) {
        if(initMSSQL()){
            String sql = "delete FROM IDBox_Master.dbo.stat_month_userPortrait where StatYear = ? and StatMonth = ?";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,year);
                preparedStatement.setInt(2,month);
                int num = preparedStatement.executeUpdate();
                logger.info("删除用户画像数据{}条",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }

    }

    /**
     * 1.获取基础字段值
     * @param statTime 包含三个统计时间的字符串元素的数组，用户画像主要使用数组中第一和第三个时间点
     * @return
     */
    private void getUserBaseField(String[] statTime, Map<String, UserPortrait> userPortraitMap){
        if(initMSSQL()){
            ResultSet resultSet = null;
            try{
                //获取统计标记的年月
                int year = ProcessTime.getYear(statTime[1]);
                int month = ProcessTime.getMonth(statTime[1]);
                String sql = "select a.VID,b.BoxNum,? as Year,? as Month,b.platenum as PlateNum,b.pl as VehicleVolume,排量 as VehicleVolumeNum,品牌 as VehicleBrand,(CASE WHEN SUBSTRING (b.PlateNum, LEN(b.PlateNum), 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') THEN '0' ELSE SUBSTRING(b.PlateNum, LEN(b.PlateNum), 1)END) as TailNum from (select distinct VID from rtm_trip where TripStartTime >= ? and TripStartTime < ?) a left join IDBox_Base.dbo.info_vehicle b on a.VID = b.vid";

                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,year);
                preparedStatement.setInt(2,month);
                preparedStatement.setString(3,statTime[0]);
                preparedStatement.setString(4,statTime[2]);

                if(preparedStatement != null){
                    resultSet = preparedStatement.executeQuery();
                    while(resultSet.next()){
                        UserPortrait up = new UserPortrait();
                        String vid = resultSet.getString("VID");
                        up.setvID(vid);
                        up.setBoxNum(resultSet.getString("BoxNum"));
                        up.setStatYear(Integer.parseInt(resultSet.getString("Year")));
                        up.setStatMonth(Integer.parseInt(resultSet.getString("Month")));
                        up.setPlateNum(resultSet.getString("PlateNum"));
                        up.setVehicleVolume(resultSet.getString("VehicleVolume"));
                        up.setVehicleVolumeNum(resultSet.getString("VehicleVolumeNum"));
                        up.setVehicleBrand(resultSet.getString("VehicleBrand"));
                        up.setTailNum(resultSet.getString("TailNum"));
                        //统一设为1，后面再对京牌车进行sql更新
                        up.setBoxWeight(1.0);

                        if(!userPortraitMap.containsKey(vid)){
                            userPortraitMap.put(vid,up);
                        }
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    /**
     * 2.获取居住地坐标、POI、所处区域及环路
     * @param statTime 统计时间
     * @param userPortraitMap 已存有部分信息的用户画像的实体类
     * @return
     */
    private void getHomeCoordinate(String[] statTime, Map<String, UserPortrait> userPortraitMap) {

        String sql = "SELECT VID,TripStartLon,TripStartLat,PrevStopTime FROM (" +
                "SELECT ROW_NUMBER () OVER (" +
                "PARTITION BY VID,CONVERT (VARCHAR (50),TripStartTime,23)" +
                "ORDER BY TripStartTime) as rank,VID,BoxNum,TripStartLon,TripStartLat,TripStartTime,PrevStopTime" +
                " FROM rtm_trip " +
                "WHERE VID IS NOT NULL " +
                "AND TripStartTime >= ? AND TripStartTime < ? AND CONVERT (INT,datename(HOUR, TripStartTime)) > 4 AND CONVERT (INT,datename(HOUR, TripStartTime)) < 12 AND TripStartLon IS NOT NULL AND PrevDistance < 150) dt WHERE dt.rank= 1 ORDER BY VID,TripStartTime";

        Map<String,List> coordinateMap = getCoordinateMap(sql, statTime[0], statTime[2]);

        int count = 0;
        int totalNumber = coordinateMap.size();
        long initTime = System.currentTimeMillis();
        for(String key : coordinateMap.keySet()){
            if(count%1000 == 0){
                long time1 = System.currentTimeMillis();
                logger.info("Precessing {} VID consume {}ms",count,time1-initTime);
                initTime = System.currentTimeMillis();
            }
            count++;
            logger.debug("Processing VID {}/{} :{}",count,totalNumber,key);

            long startTime = System.currentTimeMillis();
            PointCoordinate pointCoordinate= ProcessPoint.getCenterPoint(coordinateMap.get(key));
            long endTime = System.currentTimeMillis();
            logger.debug("VID {} get center point consume {}ms",key,endTime - startTime);
            UserPortrait userPortrait = userPortraitMap.get(key);

            if(userPortrait != null){
                userPortrait.setHome_lon(pointCoordinate.getLon());
                userPortrait.setHome_lat(pointCoordinate.getLat());
                //获取poi
                startTime = System.currentTimeMillis();
                //构造一个点，用于查询点所在的POI
                Point point = new Point(userPortrait.getHome_lon(),userPortrait.getHome_lat());
                TreeMap poiTree = preprocessPOIData.getPoi(poiIndexList,point);
                endTime= System.currentTimeMillis();
                logger.debug("VID {} get POI consume {}ms",key,endTime - startTime);
                if(poiTree != null && poiTree.size() > 0){
                    POI poi = (POI)poiTree.get(poiTree.firstKey());
                    String poiid = poi.getPoiid();
                    userPortrait.setPoi_home(poiid);
                    String distID = poiid.substring(14,16);
                    if(distID == "03"){
                        distID = "02";
                    }else if(distID == "04"){
                        distID = "01";
                    }
                    userPortrait.setHome_dist(distID);
                    userPortrait.setHome_street(poiid.substring(14,18));
                    userPortrait.setHome_ring(poiid.substring(20,23));
                }else{
                    startTime = System.currentTimeMillis();
                    //获取区域街道和环路
                    MultiPolygonStreetArea multiPolygonStreetArea = getStreetArea(pointCoordinate);
                    endTime= System.currentTimeMillis();
                    logger.debug("VID {} get id of street and area consume {}ms",key,endTime - startTime);
                    if(multiPolygonStreetArea != null){
                        String distID = multiPolygonStreetArea.getAreaID();
                        //区域id值03和04是宣武区崇文区合并入东西城
                        if(distID == "03"){
                            distID = "02";
                        }else if(distID == "04"){
                            distID = "01";
                        }
                        String streetID = multiPolygonStreetArea.getStreetID();
                        userPortrait.setHome_dist(distID);
                        userPortrait.setHome_street(streetID);
                    }

                    startTime = System.currentTimeMillis();
                    String ringID = getRing(pointCoordinate);
                    endTime= System.currentTimeMillis();
                    logger.debug("VID {} get id of ring consume {}ms",key,endTime - startTime);
                    if(ringID != null){
                        userPortrait.setHome_ring(ringID);
                    }
                }
            }
        }
    }


    /**
     * 3.获取工作地坐标、POI、所处区域及环路
     * @param statTime 统计时间
     * @param userPortraitMap 已存有部分信息的用户画像的实体类
     * @return
     */
    private void getWorkCoordinate(String[] statTime, Map<String, UserPortrait> userPortraitMap) {
        String sql = "SELECT VID,TripStartLon,TripStartLat,PrevStopTime " +
                "FROM rtm_trip WHERE VID IS NOT NULL " +
                "AND TripStartTime >= ? AND TripStartTime < ? " +
                "AND CONVERT (INT,datename(HOUR, TripStartTime)) > 9 " +
                "AND CONVERT (INT,datename(HOUR, PrevTripEndTime)) <= 17 " +
                "AND DATEPART(weekday, TripStartTime) NOT IN (1, 7) " +
                "AND PrevDistance < 150 AND TripLen > 150 AND PrevStopTime < 960 " +
                "AND TripStartLon IS NOT NULL ORDER BY VID,TripEndTime";
        Map<String,List> coordinateMap = getCoordinateMap(sql,statTime[0],statTime[2]);

        for(String key : coordinateMap.keySet()){
            UserPortrait userPortrait = userPortraitMap.get(key);

            if(userPortrait != null){
                List coordinateList = coordinateMap.get(key);
                List alterCoordinate = new ArrayList();
                //判断居住地坐标是否存在，如果有，过滤工作地备选点
                if(userPortrait.getHome_lon() != null && userPortrait.getHome_lat() != null){
                    double homeLon = userPortrait.getHome_lon().doubleValue();
                    double homeLat = userPortrait.getHome_lat().doubleValue();
                    if(homeLat > 0 && homeLon > 0){
                        for(int i=0; i< coordinateList.size(); i++){
                            PointCoordinate workCoordinate =  (PointCoordinate)coordinateList.get(i);
                            //删除与居住地之间的距离小于500米的工作地备选点
                            if(ProcessPoint.getDistance(workCoordinate.getLon(),workCoordinate.getLat(),homeLon,homeLat) > 500){
                                alterCoordinate.add(coordinateList.get(i));
                            }
                        }
                    }
                }

                PointCoordinate coord = ProcessPoint.getWorkCenterPoint(alterCoordinate);
                if(coord != null){
                    userPortrait.setWork_lon(coord.getLon());
                    userPortrait.setWork_lat(coord.getLat());
                    Point point = new Point(userPortrait.getWork_lon(),userPortrait.getWork_lat());
                    TreeMap poiTree = preprocessPOIData.getPoi(poiIndexList,point);
                    if(poiTree != null && poiTree.size() > 0){
                        POI poi = (POI)poiTree.get(poiTree.firstKey());
                        String poiid = poi.getPoiid();
                        userPortrait.setPoi_work(poiid);
                        String distID = poiid.substring(14,16);
                        if(distID == "03"){
                            distID = "02";
                        }else if(distID == "04"){
                            distID = "01";
                        }
                        userPortrait.setWork_dist(distID);
                        userPortrait.setWork_street(poiid.substring(14,18));
                        userPortrait.setWork_ring(poiid.substring(20,23));
                    }else{
                        //获取区域街道和环路
                        MultiPolygonStreetArea multiPolygonStreetArea = getStreetArea(coord);
                        if(multiPolygonStreetArea != null){
                            String distID = multiPolygonStreetArea.getAreaID();
                            //区域id值03和04是宣武区崇文区合并入东西城
                            if(distID == "03"){
                                distID = "02";
                            }else if(distID == "04"){
                                distID = "01";
                            }
                            String streetID = multiPolygonStreetArea.getStreetID();
                            userPortrait.setWork_dist(distID);
                            userPortrait.setWork_street(streetID);
                        }

                        String ringID = getRing(coord);
                        if(ringID != null){
                            userPortrait.setWork_ring(ringID);
                        }
                    }
                }
            }
        }
    }

    /**
     * 获取点所在的街道和区域
     * @param pointCoordinate
     * @return
     */
    private MultiPolygonStreetArea getStreetArea(PointCoordinate pointCoordinate){
        double x = pointCoordinate.getLon(), y = pointCoordinate.getLat();

        GeometryFactory geometryFactory = new GeometryFactory();
        Coordinate coordinate = new Coordinate(x,y);
        com.vividsolutions.jts.geom.Point point = geometryFactory.createPoint(coordinate);

        for(int i = 0; i < streetAreaArray.size(); i++){
            MultiPolygonStreetArea multiPolygonStreetArea = streetAreaArray.get(i);
            if(ProcessPoint.polygonContainsPoint(point,multiPolygonStreetArea.getMultiPolygon())){
                return multiPolygonStreetArea;
            }
        }
        return null;
    }


    /**
     * 获取点所在的环路
     * @param pointCoordinate
     * @return
     */
    private String getRing(PointCoordinate pointCoordinate){
        double x = pointCoordinate.getLon(), y = pointCoordinate.getLat();

        GeometryFactory geometryFactory = new GeometryFactory();
        Coordinate coord = new Coordinate(x,y);
        com.vividsolutions.jts.geom.Point point = geometryFactory.createPoint(coord);

        for(int i = 0; i < ringArray.size(); i++){
            MultiPolygonRing multiPolygonRing = ringArray.get(i);
            if(ProcessPoint.polygonContainsPoint(point,multiPolygonRing.getRingMultiPolygon())){
                return multiPolygonRing.getRingID();
            }
        }
        return null;
    }

    /**
     * 4.获取其他和月度相关的字段信息，如月均出行距离
     * @param statTime 统计时间，一个月
     * @param userPortraitMap
     * @return
     */
    private void getMonthFieldInfo(String[] statTime, Map<String, UserPortrait> userPortraitMap){
        if(initMSSQL()){
            String sql = "select VID,sum(triplen) as sumTripLen,count(vid) as MonthTripNum,sum(triplen)/count(VID) as TimesAvgTripLen,sum(NextStopTime)/30 as DailyAvgStopTime,sum(TripTime)/30 as DailyAvgTripTime,sum(TripSpeed)/count(VID) as AvgSpeed " +
                    "from rtm_trip where TripStartTime >= ? AND TripStartTime < ? " +
                    "GROUP BY vid";

            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);

                ResultSet rs = preparedStatement.executeQuery();
                while(rs.next()){
                    String vid = rs.getString("VID");
                    UserPortrait userPortrait = userPortraitMap.get(vid);
                    assert(userPortrait != null);
                    if(userPortrait != null){
                        userPortrait.setSumTripLen(rs.getString("sumTripLen"));
                        userPortrait.setMonthTripNum(Integer.parseInt(rs.getString("MonthTripNum")));
                        userPortrait.setTimesAvgTripLen(rs.getString("TimesAvgTripLen"));
                        userPortrait.setDailyAvgStopTime(rs.getString("DailyAvgStopTime"));
                        userPortrait.setDailyAvgTripTime(rs.getString("DailyAvgTripTime"));
                        userPortrait.setAvgSpeed(rs.getString("AvgSpeed"));
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 5.获取出行特征
     * @param statTime
     * @param userPortraitMap
     * @return
     */
    private void getTripFeature(String[] statTime, Map<String, UserPortrait> userPortraitMap){
        String sql,tripFeatureValue;
        //01上班族
        sql = "Select vid from rtm_trip where tripPurpose = 4 and TripStartTime >= ? AND TripStartTime < ? group by vid having count(vid) > 10";
        tripFeatureValue = TripFeature.work;
        updateSingleTripFeature(userPortraitMap,sql,tripFeatureValue,statTime[1],statTime[2]);

        //02购物狂
        sql = "Select vid from rtm_trip where tripPurpose = 10 and TripStartTime >= ? AND TripStartTime < ? group by vid having count(vid) > 5";
        tripFeatureValue = TripFeature.shopping;
        updateSingleTripFeature(userPortraitMap,sql,tripFeatureValue,statTime[1],statTime[2]);

        //03新手、04快车族
        updateTripDriveFeature(statTime,userPortraitMap);

        //05夜猫子
        sql = "Select vid from rtm_trip where (datename(hour,TripStartTime)>=22 or datename(hour,TripStartTime)<=5) and TripStartTime >= ? AND TripStartTime < ? group by vid having count(vid)>5";
        tripFeatureValue = TripFeature.sleep;
        updateSingleTripFeature(userPortraitMap,sql,tripFeatureValue,statTime[1],statTime[2]);

        //06专车司机
        String homeRing,tripFeature;
        tripFeatureValue = TripFeature.professional;
        for(String vid : userPortraitMap.keySet()){
            UserPortrait userPortrait = userPortraitMap.get(vid);
            homeRing = userPortrait.getHome_ring();
            if(homeRing != null && Integer.parseInt(homeRing) < 121){
                if(userPortrait.getSumTripLen()!= null && Double.valueOf(userPortrait.getSumTripLen()) > 30*10000){
                    tripFeature = userPortrait.getTripFeature();
                    if(tripFeature != null && tripFeature != ""){
                        userPortrait.setTripFeature(tripFeature+","+tripFeatureValue);
                    }else{
                        userPortrait.setTripFeature(tripFeatureValue);
                    }
                }
            }
        }
    }

    /**
     * 6.获取京牌车辆的权重系数
     * @param userPortraitMap
     */
    private void getBoxWeight(Map<String, UserPortrait> userPortraitMap){
        HashMap<Integer,Integer> streetModCarNum = getStreetModCarNum();
        HashMap<Integer,Integer> streetRealCarNum = new HashMap<>();

        //循环用户画像实体类，确定实各街道实际出现的车辆
        for(String vid : userPortraitMap.keySet()){
            UserPortrait userPortrait = userPortraitMap.get(vid);
            //判断是否是京牌车
            if(userPortrait.getPlateNum() != null && userPortrait.getPlateNum().contains("京")){
                //判断街道ID是否存在
                if(userPortrait.getHome_street() != null && userPortrait.getHome_street().length() > 0){
                    int streetID = Integer.parseInt(userPortrait.getHome_street());
                    if(streetRealCarNum.containsKey(streetID)){
                        streetRealCarNum.replace(streetID,streetRealCarNum.get(streetID) + 1);
                    }else{
                        streetRealCarNum.put(streetID,1);
                    }
                }
            }
        }

        //循环更新权重值
        for(String vid : userPortraitMap.keySet()) {
            UserPortrait userPortrait = userPortraitMap.get(vid);
            if(userPortrait.getHome_street() != null && userPortrait.getHome_street() != ""){
                int streetID = Integer.parseInt(userPortrait.getHome_street());
                if (streetRealCarNum.containsKey(streetID) && streetModCarNum.containsKey(streetID)) {
                    double boxWeight = streetModCarNum.get(streetID) / streetRealCarNum.get(streetID);
                    userPortrait.setBoxWeight(boxWeight);
                }
            }
        }
    }

    /**
     * 7.将用户画像数据存入数据库中
     * @param userPortraitMap
     */
    private void addUserPortraitData(Map<String, UserPortrait> userPortraitMap) {
        if(initMSSQL()){
            String sql = "insert into IDBox_Master.dbo.stat_month_userPortrait (StatYear,StatMonth,VID,PlateNum,TailNum,BoxNum,BoxWeight,VehicleVolume,VehicleVolumeNum,VehicleBrand,poi_home,home_dist,home_street,home_ring,home_lon,home_lat,poi_work,work_dist,work_street,work_ring,work_lon,work_lat,TimesAvgTripLen,MonthTripNum,DailyAvgTripTime,TripFeature,sumTripLen,DailyAvgStopTime,AvgSpeed) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try{
                preparedStatement = connection.prepareStatement(sql);
                int i = 0;
                for(String vid : userPortraitMap.keySet()){
                    UserPortrait userPortrait = userPortraitMap.get(vid);
                    preparedStatement.setInt(1,userPortrait.getStatYear());
                    preparedStatement.setInt(2,userPortrait.getStatMonth());
                    preparedStatement.setString(3,userPortrait.getvID());
                    preparedStatement.setString(4,userPortrait.getPlateNum());
                    preparedStatement.setString(5,userPortrait.getTailNum());
                    preparedStatement.setString(6,userPortrait.getBoxNum());
                    preparedStatement.setDouble(7,userPortrait.getBoxWeight());
                    preparedStatement.setString(8,userPortrait.getVehicleVolume());
                    preparedStatement.setString(9,userPortrait.getVehicleVolumeNum());
                    preparedStatement.setString(10,userPortrait.getVehicleBrand());
                    preparedStatement.setString(11,userPortrait.getPoi_home());
                    preparedStatement.setString(12,userPortrait.getHome_dist());
                    preparedStatement.setString(13,userPortrait.getHome_street());
                    preparedStatement.setString(14,userPortrait.getHome_ring());
                    if(userPortrait.getHome_lon() != null){
                        preparedStatement.setDouble(15,userPortrait.getHome_lon());
                    }else{
                        preparedStatement.setNull(15,Types.DOUBLE);
                    }
                    if(userPortrait.getHome_lat() != null){
                        preparedStatement.setDouble(16,userPortrait.getHome_lat());
                    }else{
                        preparedStatement.setNull(16,Types.DOUBLE);
                    }
                    preparedStatement.setString(17,userPortrait.getPoi_work());
                    preparedStatement.setString(18,userPortrait.getWork_dist());
                    preparedStatement.setString(19,userPortrait.getWork_street());
                    preparedStatement.setString(20,userPortrait.getWork_ring());
                    if(userPortrait.getWork_lon() != null){
                        preparedStatement.setDouble(21,userPortrait.getWork_lon());
                    }else{
                        preparedStatement.setNull(21, Types.DOUBLE);
                    }
                    if(userPortrait.getWork_lat() != null){
                        preparedStatement.setDouble(22,userPortrait.getWork_lat());
                    }else{
                        preparedStatement.setNull(22,Types.DOUBLE);
                    }
                    preparedStatement.setString(23,userPortrait.getTimesAvgTripLen());
                    if(userPortrait.getMonthTripNum() != null){
                        preparedStatement.setInt(24,userPortrait.getMonthTripNum());
                    }else{
                        preparedStatement.setNull(24,Types.INTEGER);
                    }
                    preparedStatement.setString(25,userPortrait.getDailyAvgTripTime());
                    preparedStatement.setString(26,userPortrait.getTripFeature());
                    preparedStatement.setString(27,userPortrait.getSumTripLen());
                    preparedStatement.setString(28,userPortrait.getDailyAvgStopTime());
                    preparedStatement.setString(29,userPortrait.getAvgSpeed());

                    preparedStatement.addBatch();
                    i++;
                    if( i%100 == 0 ){
                        preparedStatement.executeBatch();
                    }
                }
                preparedStatement.executeBatch();
                connection.commit();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库中各个街道修正后的车辆数
     */
    private HashMap<Integer,Integer> getStreetModCarNum(){
        HashMap<Integer,Integer> streetModCarNum = new HashMap<>(512);
        if(initMSSQL()){
            String sql = "select SteetID as StreetID,ModCarNum from IDBox_Base.dbo.BJ_Area_Street_2010";
            ResultSet resultSet = null;

            try{
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
                    streetModCarNum.put(resultSet.getInt("StreetID"),resultSet.getInt("ModCarNum"));
                }

            }catch (Exception e){
                e.printStackTrace();
            }
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return streetModCarNum;
    }
    /**
     * 更新出行的驾驶特征，是新手还是快车族
     * @param statTime
     * @param userPortraitMap
     */
    private void updateTripDriveFeature(String[] statTime, Map<String, UserPortrait> userPortraitMap) {
        if(initMSSQL()){
            double totalSpeed=0;
            int totalNum=0;
            String vid="",tripFeature="",tripFeatureValue="";
            Map<String,String> speedMap = new HashMap<>(1024);
            String sql = "SELECT vid,sum(tripspeed) as tripSpeedSum,count(vid) as num,SUM(TripSpeed)/COUNT(VID) as tripAvgSpeed FROM rtm_trip WHERE TripStartTime >= ? AND TripStartTime < ? AND ((CONVERT (VARCHAR, TripStartTime, 8) >= '07:00:00' AND CONVERT (VARCHAR, TripStartTime, 8) < '09:00:00') OR (CONVERT (VARCHAR, TripStartTime, 8) >= '17:00:00' AND CONVERT (VARCHAR, TripStartTime, 8) < '19:00:00'))GROUP BY VID";
            try {
                preparedStatement = connection.prepareStatement(sql);

                preparedStatement.setString(1,statTime[1]);
                preparedStatement.setString(2,statTime[2]);
                ResultSet rs = preparedStatement.executeQuery();
                while(rs.next()){
                    vid = rs.getString("vid");
                    //六环内
                    String homeRing = userPortraitMap.get(vid).getHome_ring();
                    if(homeRing != null && Integer.parseInt(homeRing) < 121){
                        speedMap.put(vid,rs.getString("tripAvgSpeed"));
                        totalSpeed += rs.getDouble("tripSpeedSum");
                        totalNum += rs.getInt("num");
                    }
                }
                if(totalNum > 0){
                    double totalNetworkAvgSpeed = totalSpeed/totalNum;
                    for(String vehicleID : speedMap.keySet()){
                        if(totalNetworkAvgSpeed - Double.valueOf(speedMap.get(vehicleID)).doubleValue() >= 2){
                            tripFeatureValue = TripFeature.novice;

                        }else if(Double.valueOf(speedMap.get(vehicleID)) - totalNetworkAvgSpeed >= 2){
                            tripFeatureValue = TripFeature.veteran;
                        }
                        UserPortrait up = userPortraitMap.get(vehicleID);
                        tripFeature = up.getTripFeature();
                        if(tripFeature != null && tripFeature != ""){
                            up.setTripFeature(tripFeature+","+tripFeatureValue);
                        }else{
                            up.setTripFeature(tripFeatureValue);
                        }
                    }
                }else{
                    logger.debug("Total network average speed is not found : 404");
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 更新单个出行特征，适用于上班族、购物狂和夜猫子三种类型
     * @param userPortraitMap
     * @param sql
     * @param tripFeatureValue
     */
    private void updateSingleTripFeature(Map<String, UserPortrait> userPortraitMap, String sql, String tripFeatureValue,String time1,String time2) {
        if(initMSSQL()){
            ResultSet rs = null;
            String vid,tripFeature,homeRing;
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,time1);
                preparedStatement.setString(2,time2);

                //01上班族
                rs = preparedStatement.executeQuery();
                while(rs.next()){
                    vid = rs.getString("vid");
                    if(vid != null && vid != ""){
                        UserPortrait userPortrait = userPortraitMap.get(vid);
                        tripFeature = userPortrait.getTripFeature();
                        homeRing = userPortrait.getHome_ring();
                        //六环内
                        if(homeRing != null && Integer.parseInt(homeRing) < 121){
                            if(tripFeature != null && tripFeature != ""){
                                userPortrait.setTripFeature(tripFeature+","+tripFeatureValue);
                            }else{
                                userPortrait.setTripFeature(tripFeatureValue);
                            }
                        }
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    /**
     * 获取工作地居住地坐标点所处区域和环路
     * @param lon
     * @param lat
     * @return
     */
    private Map<String,String> getAreaAndRing(double lon,double lat){

        int numY = (int)Math.floor((lon - 115.423411) / 0.0025);
        int numX = (int)Math.floor((lat - 39.442758) / 0.0025);
        String sql = String.format("select ID_POI,RingID,StreetID from IDBox_Base.dbo.info_grid where NumX = %s and NumY = %s",numX,numY);

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        String poi = null;
        Map<String,String> hashMap = new HashMap<>(3);
        try {
            conn = SqlServerAccess.getInstance();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
                hashMap.put("distID",rs.getString("ID_POI"));
                hashMap.put("ringID",rs.getString("RingID"));
                hashMap.put("streetID",rs.getString("StreetID"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return hashMap;
    }

    /**
     * 获取居住地/工作地备选的坐标点
     * @param sql 查询sql
     * @return
     */
    private Map<String,List> getCoordinateMap(String sql, String t1, String t2){
        ResultSet rs = null;
        //以vid作为map的key，以Double类型的坐标值数组组成的list作为value，如此获取了所有车辆统计周期内的可能是居住地的坐标值
        initMSSQL();
        Map<String,List> coordinateMap = new HashMap<>(1024);
        try {
            if( connection != null ) {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,t1);
                preparedStatement.setString(2,t2);
            }

            if( preparedStatement != null ) {
                rs = preparedStatement.executeQuery();
                while (rs.next()) {
                    PointCoordinate pointCoordinate = new PointCoordinate();
                    String vid = rs.getString("VID");
                    pointCoordinate.setLon(rs.getDouble("TripStartLon"));
                    pointCoordinate.setLat(rs.getDouble("TripStartLat"));
                    pointCoordinate.setTimeInterval(rs.getInt("PrevStopTime"));

                    if (coordinateMap.containsKey(vid)) {
                        coordinateMap.get(vid).add(pointCoordinate);
                    } else {
                        List coordinateList = new ArrayList();
                        coordinateList.add(pointCoordinate);
                        coordinateMap.put(vid, coordinateList);
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        if( rs != null ){
            try{
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return coordinateMap;
    }
}
